package id.co.shuba.spamobile.data;

import java.text.ParseException;
import java.util.Date;

import id.co.shuba.spamobile.App;
import id.co.shuba.spamobile.model.DSARInfo;
import id.co.shuba.spamobile.model.MaintenanceVisitInfo;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class MaintenanceVisitDB extends BaseDB {
	public MaintenanceVisitDB(DatabaseHandler db) {
		super(db);
	}

	public Cursor getList(String aoCode) {
		String sql = "SELECT 1 AS _id, V.VISIT_ID, V.DEBTOR_NAME, V.DATE_VISIT,"
				+ "O.STD_TEXT AS OBJECTIVE_OF_VISIT "
				+ "FROM TBL_MAINTENANCE_VISIT V "
				+ "INNER JOIN TBL_MS_STANDARD O ON O.STD_GROUP = ? AND O.STD_VALUE = V.OBJECTIVE_OF_VISIT WHERE V.AO_CODE = ? AND IS_DELETED = '0'";				

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[] { StandardDB.GROUP_OBJECTIVE_OF_VISIT, aoCode });

		return cursor;
	}	
	
	public Cursor getList(String aoCode, String filter, String sort, int type) {
		String sql = "SELECT 1 AS _id, V.VISIT_ID, V.DEBTOR_NAME, V.DATE_VISIT,"
				+ "O.STD_TEXT AS OBJECTIVE_OF_VISIT "
				+ "FROM TBL_MAINTENANCE_VISIT V "
				+ "INNER JOIN TBL_MS_STANDARD O ON O.STD_GROUP = ? AND O.STD_VALUE = V.OBJECTIVE_OF_VISIT WHERE V.AO_CODE = '"+aoCode+"' AND IS_DELETED = '0'";				

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[] { StandardDB.GROUP_OBJECTIVE_OF_VISIT });

		return cursor;
	}	
	
	public Cursor getList(String aoCode, String filter, String sort) {
		String sql = "SELECT 1 AS _id, V.VISIT_ID, V.DEBTOR_NAME, V.DATE_VISIT,"
				+ "O.STD_TEXT AS OBJECTIVE_OF_VISIT "
				+ "FROM TBL_MAINTENANCE_VISIT V "
				+ "INNER JOIN TBL_MS_STANDARD O ON O.STD_GROUP = ? AND O.STD_VALUE = V.OBJECTIVE_OF_VISIT WHERE "
				+ "V.AO_CODE = '"+aoCode+"' AND IS_DELETED = '0'";		
		
		if (filter != null && !filter.isEmpty())
			sql += " AND V.DEBTOR_NAME LIKE '" + filter + "%'";
		
		if (sort != null && !sort.isEmpty())
			sql += " ORDER BY " + sort;

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[] { StandardDB.GROUP_OBJECTIVE_OF_VISIT });

		return cursor;
	}	
	
	public Cursor getListExport(String aoCode) {
		String sql = "SELECT * FROM TBL_MAINTENANCE_VISIT WHERE AO_CODE = '"+aoCode+"'";						

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);

		return cursor;
	}
	
	public Cursor getListUpload() {
		String sql = "SELECT * FROM TBL_MAINTENANCE_VISIT WHERE IS_UPLOAD = '0'";						

		SQLiteDatabase db = _db.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);

		return cursor;
	}

	private static ContentValues createContentValues(MaintenanceVisitInfo info) {
		ContentValues values = new ContentValues();
		values.put("VISIT_ID", info.getId());
		values.put("AO_CODE", info.getAoCode());
		values.put("UNIT_CODE", info.getUnitCode());
		values.put("DATE_VISIT", info.getDateVisit());
		values.put("NO_ACCOUNT", info.getNoAccount());
		values.put("DEBTOR_NAME", info.getDebtorName());
		values.put("OBJECTIVE_OF_VISIT", info.getObjectiveOfVisit());
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("IS_UPLOAD", info.getIsUpload());	
		values.put("UPLOAD_BY", info.getUploadBy());
		values.put("UPLOAD_DATE", info.getUploadDate());
		return values;
	}
	
	public void delete(String id, String user) {	
		SQLiteDatabase db = _db.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("IS_DELETED", "1");			
		values.put("UPDATE_DATE", App.getProcessDateTime());			
		values.put("UPDATE_BY", user);	
		values.put("IS_UPLOAD", "0");
		
		db.update("TBL_MAINTENANCE_VISIT", values,"VISIT_ID = ?", new String[] {id});
	}

	public void add(MaintenanceVisitInfo info) {
		ContentValues values = createContentValues(info);
		values.put("ENTRY_BY", info.getEntryBy());
		values.put("ENTRY_DATE", info.getEntryDate());
		values.put("IS_DELETED", "0");
		values.put("IS_UPLOAD", "0");
		SQLiteDatabase db = _db.getWritableDatabase();

		db.insert("TBL_MAINTENANCE_VISIT", null, values);
	}

	public void update(MaintenanceVisitInfo info) {
		ContentValues values = createContentValues(info);		
		
		SQLiteDatabase db = _db.getWritableDatabase();

		db.update("TBL_MAINTENANCE_VISIT", values, "VISIT_ID = ?",
				new String[] { info.getId() });
	}

	public MaintenanceVisitInfo get(String visitID) {
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db.rawQuery(
				"SELECT * FROM TBL_MAINTENANCE_VISIT WHERE VISIT_ID = ?",
				new String[] {visitID});

		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return null;
		}

		MaintenanceVisitInfo info = new MaintenanceVisitInfo();
		info.setId(visitID);		
		info.setAoCode(cursorGetString(cursor, "AO_CODE"));
		info.setUnitCode(cursorGetString(cursor, "UNIT_CODE"));
		info.setDateVisit(cursorGetString(cursor, "DATE_VISIT"));
		info.setNoAccount(cursorGetString(cursor, "NO_ACCOUNT"));
		info.setObjectiveOfVisit(cursorGetString(cursor, "OBJECTIVE_OF_VISIT"));
		info.setDebtorName(cursorGetString(cursor, "DEBTOR_NAME"));		
		info.setEntryBy(cursorGetString(cursor, "ENTRY_BY"));
		info.setEntryDate(cursorGetString(cursor, "ENTRY_DATE"));
		info.setUpdateBy(cursorGetString(cursor, "UPDATE_BY"));
		info.setUpdateDate(cursorGetString(cursor, "UPDATE_DATE"));
		cursor.close();

		return info;
	}
	
	public static String getMaxDate(SQLiteDatabase db) {		
		Cursor cursor = db.rawQuery(
				"SELECT MAX(UPDATE_DATE) UPDATE_DATE FROM TBL_MAINTENANCE_VISIT", null);

		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return null;
		}
		
		String result = cursor.getString(cursor.getColumnIndex("UPDATE_DATE"));		
		cursor.close();

		return result;
	}
	
	public static void addOrUpdate(SQLiteDatabase db, MaintenanceVisitInfo info) throws ParseException {
		ContentValues values = createContentValues(info);				
		//values.put("DOWNLOAD_DATE", getCurrentDateTime());
		values.put("ENTRY_BY", info.getEntryBy());
		values.put("ENTRY_DATE", info.getEntryDate());
		values.put("IS_DELETED", info.getIsDeleted());
		
		Cursor cursor = db.rawQuery("SELECT UPDATE_DATE FROM TBL_MAINTENANCE_VISIT WHERE VISIT_ID = ?",
				new String[] { info.getId()});
						
		Date upd = null;
		if (cursor != null && cursor.getCount() > 0) {
			cursor.moveToFirst();
			upd = cursorGetDate(cursor, "UPDATE_DATE");			
		}
		cursor.close();						
		
		if (upd == null) {			
			db.insert("TBL_MAINTENANCE_VISIT", null, values);
		} else if ( dateFormat.parse(info.getUpdateDate()).getTime() > upd.getTime()) {
			db.update("TBL_MAINTENANCE_VISIT", values, "VISIT_ID = ?",
					new String[] { info.getId() });
		}
	}
	
	public static void updateUpload(SQLiteDatabase db, String id, String uploadBy) throws ParseException {
		ContentValues values = new ContentValues();
		values.put("IS_UPLOAD", "1");		
		values.put("UPLOAD_DATE", getCurrentDateTime());
		values.put("UPLOAD_BY", uploadBy);
		
		db.update("TBL_MAINTENANCE_VISIT", values,"VISIT_ID = ?", new String[] {id});
	}	
}
